//
//  MDGather.m
//  evidenceGather
//
//  Created by 王继伟 on 13-8-21.
//  Copyright (c) 2013年 无. All rights reserved.
//

#import "MDGather.h"
#import "GatherResult.h"
#import "TokDocManageUtilities.h"
#import "GatherResult.h"

@implementation MDGather


//数据库插入bug附件对象
-(NSString *)insertGatherRecord:(GatherResult *) gatherRecord{
    NSString *gatherRecPK=[TokDocManageUtilities getinsertPk];
    [gatherRecord setGathercode:[NSString stringWithFormat:@"%@",gatherRecPK]];
    NSString *insertSQL=[[NSString alloc] initWithFormat:@"insert into gatherResult(gathercode,remark,iscommit,commitTime,loginuser,pccode,projcode,provcode) values('%@','%@','%d','%@','%@','%@','%@','%@');",gatherRecord.gathercode,gatherRecord.remark,gatherRecord.iscommit,gatherRecord.commitTime,gatherRecord.loginuser,gatherRecord.pccode,gatherRecord.projcode,gatherRecord.provcode];
    NSLog(@"the insert sql is %@",insertSQL);
    [super.dbDelegate executeUpdate:insertSQL];
    return gatherRecord.gathercode;
}


//数据库插入bug附件对象
-(NSString *)insertGatherFile:(GatherFileVO *) gatherFile{
    NSString *gatherFilePK=[TokDocManageUtilities getinsertPk];
    [gatherFile setFilecode:[NSString stringWithFormat:@"GF%@",gatherFilePK]];
    NSString *insertSQL=[[NSString alloc] initWithFormat:@"insert into gatherFiles(filecode,filetype,filepath,filesuffix,gathertime,gathercode) values('%@',%d,'%@','%@','%@','%@');",gatherFile.filecode,gatherFile.filetype,gatherFile.filepath,gatherFile.filesuffix,gatherFile.gathertime,gatherFile.gathercode];
    NSLog(@"the insert sql is %@",insertSQL);
    [super.dbDelegate executeUpdate:insertSQL];
    return gatherFile.filecode;
}

//根据分类查询附件信息
-(NSMutableDictionary *)qryGatherResultByClsPk:(NSString *) pkProbCls{
    
    return nil;
}

//根据记录逐渐查询记录信息
-(GatherResult *)qryGatherResultByPk:(NSString *) pkGatherRecord{
    GatherResult *retvo = nil;
    if(nil != pkGatherRecord && [pkGatherRecord length] > 0){
        NSMutableString *sql = [[NSMutableString alloc]initWithString:@"select gathercode, remark,iscommit,commitTime,loginuser,pccode,projcode,provcode from gatherResult where gathercode='"];
        [sql appendString:pkGatherRecord];
        [sql appendString:@"';"];
        
        NSArray *rs = [super.dbDelegate executeQuery:sql];
        //当结果集内无对应的Bug记录时
        if(nil != rs && [rs count] == 1){
            NSDictionary *dic = [rs objectAtIndex:0];
            if(nil == retvo) {
                retvo = [[GatherResult alloc]init];
                retvo.gathercode = [[NSString alloc] initWithData:[dic objectForKey:@"gathercode"] encoding:NSUTF8StringEncoding];
                retvo.remark = [[NSString alloc] initWithData:[dic objectForKey:@"remark"] encoding:NSUTF8StringEncoding];
                retvo.iscommit = [[NSString alloc] initWithData:[dic objectForKey:@"iscommit"] encoding:NSUTF8StringEncoding];
                retvo.commitTime = [[NSString alloc] initWithData:[dic objectForKey:@"commitTime"] encoding:NSUTF8StringEncoding];
                retvo.loginuser = [[NSString alloc] initWithData:[dic objectForKey:@"loginuser"] encoding:NSUTF8StringEncoding];
                retvo.pccode = [[NSString alloc] initWithData:[dic objectForKey:@"pccode"] encoding:NSUTF8StringEncoding];
                retvo.projcode =[[NSString alloc] initWithData:[dic objectForKey:@"projcode"] encoding:NSUTF8StringEncoding];
                retvo.provcode =[[NSString alloc] initWithData:[dic objectForKey:@"provcode"] encoding:NSUTF8StringEncoding];
                
            }
        }
        [rs release];
        [sql release];
    }
    return retvo;
}

//数据库插入bug附件对象
-(GatherFileVO *)qryGatherFileByPk:(NSString *) pkGatherFile{
    
    return nil;
}

-(GatherResult *) qryUnCommitGatherInfo:(NSString *)pkProbCls projCode:(NSString *) projCode prvcode:(NSString *)prvcode loginUser:(NSString *) loginUser{
    GatherResult *retvo = nil; 
    NSMutableString *sql = [[NSMutableString alloc]initWithString:@"select gathercode, remark,iscommit,commitTime,loginuser,pccode,projcode from gatherResult where pccode='"];
    [sql appendString:pkProbCls];
    [sql appendString:@"' and projcode='"];
    [sql appendString:projCode];
    [sql appendString:@"' and provcode='"];
    [sql appendString:prvcode];
    [sql appendString:@"' and loginuser='"];
    [sql appendString:loginUser];
    [sql appendString:@"' and iscommit=0;"]; 
    
    NSArray *rs = [super.dbDelegate executeQuery:sql];
    //当结果集内无对应的Bug记录时
    if(nil != rs && [rs count] == 1){
        NSDictionary *dic = [rs objectAtIndex:0];
        if(nil == retvo) {
            retvo = [[GatherResult alloc]init];
            retvo.gathercode = [[NSString alloc] initWithData:[dic objectForKey:@"gathercode"] encoding:NSUTF8StringEncoding];
            retvo.remark = [[NSString alloc] initWithData:[dic objectForKey:@"remark"] encoding:NSUTF8StringEncoding];
            retvo.iscommit = [[NSString alloc] initWithData:[dic objectForKey:@"iscommit"] encoding:NSUTF8StringEncoding];
            retvo.commitTime = [[NSString alloc] initWithData:[dic objectForKey:@"commitTime"] encoding:NSUTF8StringEncoding];
            retvo.loginuser = [[NSString alloc] initWithData:[dic objectForKey:@"loginuser"] encoding:NSUTF8StringEncoding];
            retvo.pccode = [[NSString alloc] initWithData:[dic objectForKey:@"pccode"] encoding:NSUTF8StringEncoding];
            retvo.projcode =[[NSString alloc] initWithData:[dic objectForKey:@"projcode"] encoding:NSUTF8StringEncoding];
            retvo.provcode =[[NSString alloc] initWithData:[dic objectForKey:@"provcode"] encoding:NSUTF8StringEncoding];
        }
    }
    [rs release];
    [sql release];
    return retvo;
}

//根据项目编码和问题查询所有采样记录
-(NSMutableDictionary *)qryGatherRecourdByPk:(NSString *) projCode pccode:(NSString *)pccode prvcode:(NSString *)prvcode loginUser:(NSString *) loginUser{
    NSMutableDictionary *result = nil;
    GatherResult *retvo = nil;
    NSMutableString *sql = [[NSMutableString alloc]initWithString:@"select gathercode, remark,iscommit,commitTime,loginuser,pccode,projcode from gatherResult where pccode='"];
    [sql appendString:pccode];
    [sql appendString:@"' and projcode='"];
    [sql appendString:projCode];
    [sql appendString:@"' and provcode='"];
    [sql appendString:prvcode];
    [sql appendString:@"' and loginuser='"];
    [sql appendString:loginUser];
    [sql appendString:@"';"];

    NSArray *rs = [super.dbDelegate executeQuery:sql];
    if(nil != rs && [rs count] > 0){
        //如果查询结果不为空，则初始化dictionary，并循环赋值
        NSDictionary *dic = [rs objectAtIndex:0];
        result = [[NSMutableDictionary alloc]init];
        for(int i=0;i < [rs count]; i++){
            retvo = [[GatherResult alloc]init];
            dic = [rs objectAtIndex:i];
            retvo.gathercode = [[NSString alloc] initWithData:[dic objectForKey:@"gathercode"] encoding:NSUTF8StringEncoding];
            retvo.remark = [[NSString alloc] initWithData:[dic objectForKey:@"remark"] encoding:NSUTF8StringEncoding];
            retvo.iscommit = [[NSString alloc] initWithData:[dic objectForKey:@"iscommit"] encoding:NSUTF8StringEncoding];
            retvo.commitTime = [[NSString alloc] initWithData:[dic objectForKey:@"commitTime"] encoding:NSUTF8StringEncoding];
            retvo.loginuser = [[NSString alloc] initWithData:[dic objectForKey:@"loginuser"] encoding:NSUTF8StringEncoding];
            retvo.pccode = [[NSString alloc] initWithData:[dic objectForKey:@"pccode"] encoding:NSUTF8StringEncoding];
            retvo.projcode =[[NSString alloc] initWithData:[dic objectForKey:@"projcode"] encoding:NSUTF8StringEncoding];
            retvo.provcode =[[NSString alloc] initWithData:[dic objectForKey:@"provcode"] encoding:NSUTF8StringEncoding];
            [result setObject:retvo forKey:retvo.gathercode];
        }
    }
    return result;
}



//根据项目编码统计分类下的所有采样记录
-(NSMutableDictionary *)qryGatherRecourdCountByPk:(NSString *) pkProjCode{
     NSMutableDictionary *result = nil;
    
    if(nil == pkProjCode)
        return result;
    NSMutableString *sql = [[NSMutableString alloc] initWithString:@"select count(pccode) as count,pccode from gatherResult where projcode='"];
    [sql appendString:pkProjCode];
    [sql appendString:@"' group by pccode"];
    NSArray *rs = [super.dbDelegate executeQuery:sql];
    if(nil != rs && [rs count] > 0){
        //如果查询结果不为空，则初始化dictionary，并循环赋值
        result = [[NSMutableDictionary alloc]init];
        for(int i=0;i < [rs count]; i++){
            NSDictionary *dic = [rs objectAtIndex:i];
            //获取附件对应的主键
            NSString *pccode = [[NSString alloc] initWithData:[dic objectForKey:@"pccode"] encoding:NSUTF8StringEncoding];
            int count=[[[NSString alloc] initWithData:[dic objectForKey:@"count"] encoding:NSUTF8StringEncoding] intValue];
            [result setObject:[NSString stringWithFormat:@"  %d",count] forKey:pccode];
        }
    }

    
    return result;
}

//根据项目编码统计分类下的所有采样记录
-(NSArray *)qryRecourdFileBypkRecord:(NSString *) gatherRecord{
    NSMutableArray  *result = nil;
    GatherFileVO *retvo = nil;
    NSMutableString *sql = [[NSMutableString alloc] initWithString:@"select filecode,filetype,filepath,filesuffix,gathertime,gathercode from gatherFiles where gathercode='"];
    [sql appendString:gatherRecord];
    [sql appendString:@"' order by gathertime"];
    NSArray *rs = [super.dbDelegate executeQuery:sql];
    if(nil != rs && [rs count] > 0){
        //如果查询结果不为空，则初始化dictionary，并循环赋值
        result = [[NSMutableArray alloc]init];
        for(int i=0;i < [rs count]; i++){
            retvo = [[GatherFileVO alloc]init];
            NSDictionary *dic = [rs objectAtIndex:i];
            //获取附件对应的主键
            retvo.filecode = [[[NSString alloc] initWithData:[dic objectForKey:@"filecode"] encoding:NSUTF8StringEncoding] intValue];
            retvo.filetype = [[[NSString alloc] initWithData:[dic objectForKey:@"filetype"] encoding:NSUTF8StringEncoding] intValue];
            retvo.filesuffix=[[NSString alloc] initWithData:[dic objectForKey:@"filesuffix"] encoding:NSUTF8StringEncoding];
            retvo.gathertime=[[NSString alloc] initWithData:[dic objectForKey:@"gathertime"] encoding:NSUTF8StringEncoding];
            retvo.gathercode=[[NSString alloc] initWithData:[dic objectForKey:@"gathercode"] encoding:NSUTF8StringEncoding];
            retvo.filepath=[[NSString alloc] initWithData:[dic objectForKey:@"filepath"] encoding:NSUTF8StringEncoding];
            [result addObject:retvo];
        }
    }
    return result;
}


//更新采样备注信息
-(BOOL *)updateResultMemo:(NSString *) memo gatherRecord:(NSString *)gatherRecord {
    NSMutableString *sql = [[NSMutableString alloc]initWithString:@"update gatherResult set remark='"];
    [sql appendString:memo];
    [sql appendString:@"' where gathercode='"];
    [sql appendString:gatherRecord];
    [sql appendString:@"' "];
    return [super.dbDelegate executeUpdate:sql];
    
}

//根据主键判断记录是否提交
-(BOOL *)isCommit:(NSString *) gatherReord{
    GatherResult *retvo =[self qryGatherResultByPk:gatherReord];
    if(retvo!=nil){
        int iscommit=retvo.iscommit;
        if(iscommit==0){
            return false;
        }else if(iscommit==1){
            return true;
        }
    }
    return false;
}
//更新是否提交标记信息
-(BOOL *)updateResultCommit:(NSArray *) gatherReords{
    NSMutableString *sql = [[NSMutableString alloc]initWithString:@"update gatherResult set iscommit=1"];
    [sql appendString:@" where gathercode in("];
    int count=[gatherReords count];
    for (int i=0;i<count;i++) {
        NSString *gatherRecord=[gatherReords objectAtIndex:i];
        [sql appendString:@"'"];
        [sql appendString:gatherRecord];
        [sql appendString:@"',"];
    }
    
    [sql appendString:@"1=1) "];
    return [super.dbDelegate executeUpdate:sql];
}

//更新是否提交标记信息
-(BOOL *)updateResultStatus:(NSString *) prvcode projCode:(NSString *) projCode loginUser:(NSString *) loginUser{
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    //设定时间格式,这里可以设置成自己需要的格式
    [dateFormatter setDateFormat:@"yyyyMMddHHmmss"];
    //用[NSDate date]可以获取系统当前时间
    NSString *curDateStr = [dateFormatter stringFromDate:[NSDate date]];
    //输出格式为：2010-10-27 10:22:13
    //NSLog(@”%@”,currentDateStr);
    //alloc后对不使用的对象别忘了release
        
    NSMutableString *sql = [[NSMutableString alloc]initWithString:@"update gatherResult set iscommit=1,commitTime='"];
    [sql appendString:curDateStr];
    [sql appendString:@"'  where provcode='"];
    [sql appendString:prvcode];
    [sql appendString:@"' and projCode='"];
    [sql appendString:projCode];
    [sql appendString:@"' and loginuser='"];
    [sql appendString:loginUser];
    [sql appendString:@"';"];
    [dateFormatter release];
    return [super.dbDelegate executeUpdate:sql];
}

-(void)deleteGatherResultWithProjectCode:(NSArray *)projCodes loginuser:(NSString *)loginuser
{
   
    if(nil == projCodes || 0 == [projCodes count])
    {
        return ;
    }
    NSMutableString *projCodeClause = [[NSMutableString alloc] initWithString:@" projcode in("];
    for (NSString *p_code in projCodes) {
        [projCodeClause appendFormat:@"'%@',",p_code];
    }
    NSString * in_clause =  [projCodeClause substringToIndex:[projCodeClause length] - 1];
    in_clause = [in_clause stringByAppendingString:@") "];

    //删除采集的文件
    NSMutableString *del_Files = [[NSMutableString alloc] initWithString:@"delete from gatherFiles where gathercode in (select gathercode from gatherresult where "];
    [del_Files appendString:in_clause];
    [del_Files appendFormat:@" and loginuser = '%@')",loginuser];

    [super.dbDelegate executeUpdate:del_Files];
    [del_Files release];
    //删除采集数据

    NSMutableString *del_result = [[NSMutableString alloc] initWithString:@"delete from gatherResult where "];
    [del_result appendString:in_clause];
    [del_result appendFormat:@" and loginuser = '%@'",loginuser];
    [super.dbDelegate executeUpdate:del_result];
    [del_result release];

    [projCodeClause release];
//    [in_clause release];
    
    return ;
}
@end
